/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cbsrs.controller;

import com.cbsrs.model.Users;
import com.cbsrs.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class UserDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(UserDAO.class);

    public static Users authenticate(String IDNumber, String password, String userType) {
        Users user = null;
        String sql = null;
        PreparedStatement preparedStatement;
        try {
            if (userType.equalsIgnoreCase("student")) {
                sql = "SELECT s.*, c.college_name FROM students AS s INNER JOIN colleges AS c ON c.college_id = s.college_id WHERE s.student_idno=? and s.student_password = MD5(?)";
            } else {
                sql = "SELECT u.*, c.college_name FROM users AS u INNER JOIN colleges AS c ON c.college_id = u.college_id WHERE u.user_id=? and u.user_password = MD5(?) AND u.user_status=1";
            }

            preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, IDNumber);
            preparedStatement.setString(2, password);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();

            if (userType.equalsIgnoreCase("student")) {
                while (rs.next()) {
                    user=new Users();
                    user.setIDNumber(rs.getString("student_idno"));
                    user.setFirstName(rs.getString("student_firstname"));
                    user.setMiddleName(rs.getString("student_middlename"));
                    user.setLastName(rs.getString("student_lastname"));
                    user.setUserRole("student");
                    user.setCollegeName(rs.getString("college_name"));
                    user.setEmail(rs.getString("student_email"));
                    user.setUserType("student");
                    user.setCollegeID(rs.getInt("college_id"));
                    user.setStatus(rs.getInt("student_status"));
                }
            } else {
                while (rs.next()) {
                    user=new Users();
                    user.setIDNumber(rs.getString("user_id"));
                    user.setFirstName(rs.getString("user_firstname"));
                    user.setMiddleName(rs.getString("user_middlename"));
                    user.setLastName(rs.getString("user_lastname"));
                    user.setUserRole(rs.getString("user_role"));
                    user.setCollegeName(rs.getString("college_name"));
                    user.setEmail(rs.getString("user_email"));
                    user.setUserType("user");
                    user.setCollegeID(rs.getInt("college_id"));
                    user.setStatus(rs.getInt("user_status"));
                }
            }

//            }

            log.info("USER : " + userType.toString());
        } catch (SQLException ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return user;
    }
}
